
/**
 * @author JackGao
 *			email:jackgao.china@gmail.com
 *  date：2016-6-23
 * 
 */
package cn.cuit.ds.dao.impl;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.springframework.stereotype.Repository;

import cn.cuit.ds.dao.IGoodsDao;
import cn.cuit.ds.domain.Goods;
import cn.cuit.ds.utils.JdbcUtils;
import cn.cuit.ds.utils.PageBean;

@Repository(value="GoodsDao")
public class GoodsDao implements IGoodsDao {

	//   test
	public void add(Goods goods) {
		QueryRunner runner = JdbcUtils.getQueryRunner();
		String sql = "insert into goods values(?,?,?,?,?,?,?,?)";
		try {
			runner.update(sql,goods.getGoodsId(),goods.getShoperId(),goods.getGoodsPrice(),
					goods.getShopPic(),goods.getTimeAdd(),goods.getGoodsNum(),goods.getTotalNum(),goods.getGoodsName());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Goods> getAll() {
		QueryRunner runner = JdbcUtils.getQueryRunner();
		String sql = "select * from Goods where totalNum!=0";
		try {
			return runner.query(sql,new BeanListHandler<Goods>(Goods.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public void getAll(PageBean<Goods> pb){
		int totalCount = this.getTotalCount();
		pb.setTotalCount(totalCount);
		
		/*
		 * 问题： jsp页面，如果当前页为首页，再点击上一页报错！
		 *              如果当前页为末页，再点下一页显示有问题！
		 * 解决：
		 * 	   1. 如果当前页 <= 0;       当前页设置当前页为1;
		 * 	   2. 如果当前页 > 最大页数；  当前页设置为最大页数
		 */
		// 判断
		if (pb.getCurrentPage() <=0) {
			pb.setCurrentPage(1);					    // 把当前页设置为1
		} else if (pb.getCurrentPage() > pb.getTotalPage()){
			pb.setCurrentPage(pb.getTotalPage());		// 把当前页设置为最大页数
		}
		
		//1. 获取当前页： 计算查询的起始行、返回的行数
		int currentPage = pb.getCurrentPage();
		int index = (currentPage -1 ) * pb.getPageCount();		// 查询的起始行
		int count = pb.getPageCount();							// 查询返回的行数
		
		
		//3. 分页查询数据;  把查询到的数据设置到pb对象中
		String sql ="select * from Goods where totalNum!=0 limit ?,?";
		
		try {
			// 得到Queryrunner对象
			QueryRunner runner = JdbcUtils.getQueryRunner();
			// 根据当前页，查询当前页数据(一页数据)
			List<Goods> pageData = runner.query(sql, new BeanListHandler<Goods>(Goods.class), index, count);
			// 设置到pb对象中
			pb.setPageData(pageData);
			
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
	}

	private int getTotalCount() {
		String sql = "select count(*) from Goods where totalNum!=0";
		QueryRunner runner = JdbcUtils.getQueryRunner();
		try {
			Long count = runner.query(sql, new ScalarHandler<Long>());
			return count.intValue();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public List<Goods> getAllByShopperId(String shopperId) {
		QueryRunner runner = JdbcUtils.getQueryRunner();
		String sql = "select * from goods where shoperId= ?";
		try {
			return runner.query(sql,new BeanListHandler<Goods>(Goods.class),shopperId);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public void deleteByGoodsId(String goodsId) {
		QueryRunner runner = JdbcUtils.getQueryRunner();
		String sql = "UPDATE goods set totalNum='0' where goodsId=?";
		try {
			runner.update(sql,goodsId);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public void update(Goods good) {
		QueryRunner runner = JdbcUtils.getQueryRunner();
		String sql = "update Goods SET goodsName=?,goodsNum=?,goodsPrice=?," +
				"shopPic=?,timeAdd=?,totalNum=? where goodsId=? and  shoperId=?";
		try {
			runner.update(sql,good.getGoodsName(),good.getGoodsNum(),good.getGoodsPrice(),good.getShopPic(),good.getTimeAdd(),good.getTotalNum(),good.getGoodsId(),good.getShoperId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public Goods findById(String id) {
		QueryRunner runner = JdbcUtils.getQueryRunner();
		String sql = "select * from Goods where goodsId =?";
		try {
			return runner.query(sql,new BeanHandler<Goods>(Goods.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	
	}
	/**
	 * totalNum -1  相当于卖掉了一个东西
	 * @param goodsId
	 */
	public void subTotalNum(String goodsId){
		QueryRunner runner = JdbcUtils.getQueryRunner();
		String sql = "UPDATE goods SET totalNum=totalNum-1 WHERE goodsId=?";
		try {
			runner.update(sql, goodsId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 
	 * @param goodsId
	 */
	public void addGoodsNum(String goodsId){
		QueryRunner runner = JdbcUtils.getQueryRunner();
		String sql = "UPDATE goods SET goodsNum=goodsNum+1 WHERE goodsId=?";
		try {
			runner.update(sql, goodsId);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}


}

